package com.wonders.frame.chron.dao.user.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.wonders.frame.chron.dao.user.UserInfoDao;
import com.wonders.frame.chron.model.vo.UserInfo;

@Repository("userInfoDao")
@Transactional
public class UserInfoDaoImpl implements UserInfoDao {

	private EntityManager em;

	@Resource(name = "entityManagerFactory")
	private EntityManagerFactory entityManagerFactory;

	public EntityManager getEntityManager() {
		if (em == null || !em.isOpen()) {
			em = entityManagerFactory.createEntityManager();
		}
		return em;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfo> findByGroupName(String groupName) {
		try {
			String sql = "select u.name,u.login_name loginname,u.login_organ_name loginorganname from af_relation r "
					+ "left join af_group g on r.p_id=g.id left join af_user u on r.n_id=u.id "
					+ "where p_type='group' and g.name='"+groupName+"' "
					+ "and r.removed='0' and g.removed='0' and u.removed='0'";
			Query query = getEntityManager().createNativeQuery(sql);
			query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(UserInfo.class));
			List<UserInfo> result = query.getResultList();
			return result;
		} catch (Exception e) {
			return null;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<UserInfo> findByGroupNameAndDept(String groupName, String dept) {
		try {
			String sql = "select u.name,u.login_name loginname,u.login_organ_name loginorganname from af_relation r "
					+ "left join af_group g on r.p_id=g.id left join af_user u on r.n_id=u.id "
					+ "where p_type='group' and g.name='"+groupName+"' and u.login_organ_name like '%"+dept+"' "
					+ "and r.removed='0' and g.removed='0' and u.removed='0'";
			Query query = getEntityManager().createNativeQuery(sql);
			query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(UserInfo.class));
			List<UserInfo> result = query.getResultList();
			return result;
		} catch (Exception e) {
			return null;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}

	@Override
	public Map<String,String> findByIds(Set<String> userSet) {
		try {
			Map<String,String> resultMap = new HashMap<String, String>();
			StringBuffer sb = new StringBuffer();
			for (String str : userSet) {
				sb.append("'").append(str).append("'").append(",");
			}
			
			String param = sb.toString().substring(0, sb.toString().lastIndexOf(","));
			
			//String sql = "select u.name,u.login_name loginname,u.login_organ_name loginorganname from af_user u where login_organ_name in ("+param+") and removed='0'";
			String sql = "select o.name deptname,u.name,u.login_name loginname,u.login_organ_name loginorganname from af_relation r "+
				          "join af_organ o on r.p_id=o.id join af_user u on r.n_id=u.id "+
							"where r.p_type='organ' and r.n_type='user' and u.login_organ_name in ("+param+") "+
							"and r.removed='0' and o.removed='0' and u.removed='0'";
			Query query = getEntityManager().createNativeQuery(sql);
			query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(UserInfo.class));
			List<UserInfo> result = query.getResultList();
			
			for (UserInfo userInfo : result) {
				resultMap.put(userInfo.getLOGINORGANNAME(), userInfo.getNAME());
				resultMap.put(userInfo.getLOGINORGANNAME()+"deptName", userInfo.getDEPTNAME());
			}
			return resultMap;
		} catch (Exception e) {
			return null;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}
	
	public List<String> findGroupNameByUserId(String userId) {
		try {
			String sql = "select g.name from af_relation r "
					+ "join af_group g on r.p_id=g.id join af_user u on r.n_id=u.id "
					+ "where p_type='group' and n_type='user' and u.id=:userId "
					+ "and r.removed='0' and g.removed='0' and u.removed='0'";
			Query query = getEntityManager().createNativeQuery(sql);
			query.setParameter("userId", userId);
			List<String> result = query.getResultList();
			return result;
		} catch (Exception e) {
			return null;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}

	@Override
	public boolean findUserInGroup(String userId,Set<String> userGroup) {
		try {
			StringBuffer sb = new StringBuffer();
			for (String str : userGroup) {
				sb.append("'").append(str).append("'").append(",");
			}
			
			String param = sb.toString().substring(0, sb.toString().lastIndexOf(","));
			
			String sql = "select r.id from af_relation r "+
							"join af_group g on r.p_id=g.id join af_user u on r.n_id=u.id "+
							"where p_type='group' and n_type='user' and u.id='"+userId+"' AND g.name IN("+param+") "+
							"and r.removed='0' and g.removed='0' and u.removed='0'";
			Query query = getEntityManager().createNativeQuery(sql);
			List<String> result = query.getResultList();
			if(result.size() > 0){
				return true;
			}else{
				return false;
			}
		} catch (Exception e) {
			return false;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}

	@Override
	public List<UserInfo> findLeaderByUserName(String loginName) {
		try {
			String sql = "SELECT u.name,u.login_name loginname,u.login_organ_name loginorganname FROM  af_user u ,af_ch_relation r where p_type='user' AND n_type='user' "
					+ "AND U.id=r.p_id  "
					+ "and r.removed='0' and u.removed='0' AND n_id IN( "
					+ " SELECT ID FROM af_user WHERE login_name LIKE '%"+loginName+"%')";
			Query query = getEntityManager().createNativeQuery(sql);
			query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(UserInfo.class));
			List<UserInfo> result = query.getResultList();
			return result;
		} catch (Exception e) {
			return null;
		} finally {
			if (em != null) {
				em.close();
			}
		}
	}
}
